﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.Threading;
using System.IO;
using System.Diagnostics;

namespace DBDemo
{
    class Program
    {
        public const string RUN_TYPE = "Release";
        [DllImport(@"..\..\..\" + RUN_TYPE + @"\DBMS.dll", EntryPoint = "mainInitDB", CharSet = CharSet.Ansi)]
        public static extern void mainInitDB();
        [DllImport(@"..\..\..\" + RUN_TYPE + @"\DBMS.dll", EntryPoint = "parseExe", CharSet = CharSet.Ansi)]
        public static extern void parseExe(StringBuilder cmd, ref StringBuilder result);
        [DllImport(@"..\..\..\" + RUN_TYPE + @"\DBMS.dll", EntryPoint = "mainUseDB", CharSet = CharSet.Ansi)]
        public static extern void mainUseDB(StringBuilder cmd);
        [DllImport(@"..\..\..\" + RUN_TYPE + @"\DBMS.dll", EntryPoint = "mainCloseDB", CharSet = CharSet.Ansi)]
        public static extern void mainCloseDB();
        [DllImport(@"..\..\..\" + RUN_TYPE + @"\DBMS.dll", EntryPoint = "mainImportData", CharSet = CharSet.Ansi)]
        public static extern void mainImportData(StringBuilder filename, StringBuilder tablename);


        static void Main(string[] args)
        {
            File.Delete("test.db");
            File.Delete("test.sys");

            Console.WriteLine("Welcome to our DBMS");
            mainInitDB();
            StringBuilder arg1 = new StringBuilder("test");
            StringBuilder arg2 = new StringBuilder();
            Stopwatch time;
            
            Console.WriteLine("usedb test");
            mainUseDB(arg1);
            Console.WriteLine("DB test used!");
            Console.ReadKey(false);

            string[] create ={"CREATE TABLE t_Class (\n\t[班号] [char] (8) NOT NULL ,\n\t[班名称] [varchar] (80) NULL ,\n\t[所属院系号] [char] (2) NULL ,\n\t[所属专业号] [char] (3) NULL \n)",
                                 "CREATE TABLE t_Course (\n\t[课程号] [char] (11) NOT NULL ,\n\t[课程名] [varchar] (100) NULL ,\n\t[开课院系号] [char] (2) NOT NULL ,\n\t[课程类别] [char] (1) NULL ,\n\t[学时] [int] NULL ,\n\t[考核方式] [char] (1) NULL\n)",
                                 "CREATE TABLE t_Department (\n\t[院系号] [char] (2) NOT NULL ,\n\t[院系名] [varchar] (60) NULL\n)",
                                 "CREATE TABLE t_Score (\n\t[成绩号] [char] (23) NOT NULL ,\n\t[学号] [char] (11) NOT NULL ,\n\t[课程号] [char] (11) NOT NULL ,\n\t[平时成绩] [varchar] (10) NULL ,\n\t[实验成绩] [varchar] (10) NULL ,\n\t[期末成绩] [varchar] (10) NULL ,\n\t[客观成绩] [varchar] (10) NULL ,\n\t[总成绩] [varchar] (10) NULL ,\n\t[补考成绩] [varchar] (10) NULL ,\n\t[重修成绩] [varchar] (10) NULL ,\n\t[成绩状态] [char] (1) NULL \n)",
                                 "CREATE TABLE t_Specialty (\n\t[专业号] [char] (3)   NOT NULL ,\n\t[专业名] [varchar] (50) NULL ,\n\t[所属院系号] [char] (2) NULL ,\n\t[修业最低年限] [int] NULL ,\n\t[修业最高年限] [int] NULL \n)",
                                 "CREATE TABLE t_Stu (\n\t[学号] [char] (11) NOT NULL ,\n\t[姓名] [varchar] (12) NULL ,\n\t[姓名首字母] [varchar] (6) NULL ,\n\t[英文姓名] [varchar] (26) NULL ,\n\t[性别] [char] (1) NULL ,\n\t[所属院系号] [char] (2) NULL ,\n\t[所属专业号] [char] (3)  NULL ,\n\t[所属班号] [char] (8) NULL ,\n\t[学生类别] [char] (2) NULL ,\n\t[入学年月] [char] (6) NULL ,\n\t[出生日期] [char] (8) NULL ,\n\t[健康状况] [char] (2) NULL ,\n\t[婚姻状况] [char] (1) NULL \n)",
                                 "CREATE TABLE t_StuCourse (\n\t[选课号] [char] (23) NOT NULL ,\n\t[选课学生号] [char] (11) NOT NULL ,\n\t[所选课程号] [char] (11) NOT NULL ,\n\t[选课IP] [varchar] (15) NULL ,\n\t[交费情况] [char] (1)  NULL\n)"
                             };

            Console.Clear();
            for (int i = 0; i < create.Length; i++)
            {
                arg1 = new StringBuilder(create[i]);
                Console.WriteLine(create[i]);
                Console.ReadKey(false);
                parseExe(arg1, ref arg2);
                Console.WriteLine(arg2);
                Console.ReadKey(false);
                Console.Clear();
            }

            string[] filename = { "t_Class.txt", "t_Course.txt", "t_Department.txt", "t_Score.txt", "t_Specialty.txt", "t_Stu.txt", "t_StuCourse.txt" };
            string[] tablename = { "t_Class", "t_Course", "t_Department", "t_Score", "t_Specialty", "t_Stu", "t_StuCourse" };



            Console.WriteLine("Import data from text files.Press any key to continue...");
            Console.ReadKey(false);
            for (int i = 0; i < filename.Length; i++)
            {
                arg1 = new StringBuilder(filename[i]);
                arg2 = new StringBuilder(tablename[i]);

                time = new Stopwatch();
                time.Start();
                mainImportData(arg1, arg2);
                time.Stop();
                Console.WriteLine("Importing {0} finished, spend time {1} s", arg1, time.ElapsedMilliseconds / 1000.0);
            }
            Console.WriteLine("Import finished");
            Console.ReadKey(false);
            Console.Clear();
            string[] select = {"SELECT * FROM t_Course WHERE 考核方式 <> '1'",
                               "SELECT * FROM t_Department WHERE 院系名 LIKE '人%'",
                               "SELECT * FROM t_Specialty WHERE 修业最低年限 BETWEEN 3 AND 4",
                               "SELECT 姓名,性别,所属专业号 \nFROM t_Stu \nWHERE 所属院系号 = '04' AND 所属专业号='041' AND 入学年月='200309'",
                               "SELECT 学号,总成绩 \nFROM t_Score,t_Course \nWHERE t_Score.课程号 = t_Course.课程号 \nAND t_Course.课程名 = '临床综合' \nAND t_Score.总成绩 < '60'",
                               "SELECT t_Stu.姓名,t_Department.院系名,t_Stu.所属专业号,t_Stu.所属班号 \nFROM t_Stu,t_Department \nWHERE t_Stu.所属院系号 = t_Department.院系号 \nAND t_Stu.性别='2' \nAND t_Stu.出生日期>'19830101' \nAND 婚姻状况 <> '1'",
                               "SELECT 学号,总成绩,选课IP \nFROM t_Score,t_Course,t_StuCourse \nWHERE t_Score.课程号 = t_Course.课程号 \nAND t_Score.学号 = t_StuCourse.选课学生号 \nAND t_StuCourse.选课IP LIKE '218.28.170.%' \nAND t_Course.课程名 = '临床综合' \nAND t_Score.总成绩 < '60'"
                           };

            for (int i = 0; i < select.Length; i++)
            {
                arg1 = new StringBuilder(select[i]);
                Console.WriteLine(select[i]);
                Console.ReadKey(false);
                time = new Stopwatch();
                time.Start();
                parseExe(arg1, ref arg2);
                Console.WriteLine(arg2);
                time.Stop();
                Console.WriteLine("Select finished, spend time {1} s", arg1, time.ElapsedMilliseconds / 1000.0);
                Console.ReadKey(false);
                Console.Clear();
            }

            Console.Clear();


            string[] insert = {"SELECT * FROM t_Department WHERE 院系号 = '9#'",
                              "INSERT INTO t_Department \nVALUES ('9#','系~计算机科学与技术')",
                              "SELECT * FROM t_Specialty WHERE 专业号 = '911'",
                              "INSERT INTO t_Specialty (专业名,专业号,所属院系号,修业最低年限,修业最高年限) \nVALUES ('专业~并行数据挖掘','911','9#',5,8)",
                              "SELECT * FROM t_Class WHERE 班号 = '87654321'",
                              "INSERT INTO t_Class (班名称,班号,所属院系号,所属专业号) \nVALUES ('班~计研53','87654321','9#','911')",
                              "SELECT * FROM t_Stu WHERE 学号 = '2004310431 '",
                              "INSERT INTO t_Stu (姓名,学号,姓名首字母,英文姓名,所属专业号,所属班号,性别,所属院系号,学生类别,入学年月,出生日期,健康状况,婚姻状况) \nVALUES ('人~张宇宙','2004310431 ','ZYZ','Cosmos','911','87654321','1','9#','20','200409','19830101','10','1')",
                              "SELECT * FROM t_Course WHERE 课程号 = '10204db    '",
                              "INSERT INTO t_Course \nVALUES ('10204db    ','课~数据库专题训练','9#','2',88,'1')",
                              "SELECT * FROM t_StuCourse WHERE 选课号 = '2004310431@9#db62789150'",
                              "INSERT INTO t_StuCourse (选课号,选课学生号,所选课程号,选课IP,交费情况) \nVALUES ('2004310431@9#db62789150','2004310431 ','10204db    ','166.111.69.222','1')",
                              "SELECT * FROM t_Score WHERE 成绩号 = '2004310431xxxxx62789150'",
                              "INSERT INTO t_Score (成绩号,学号,课程号,平时成绩,实验成绩,期末成绩,客观成绩,总成绩,补考成绩,重修成绩,成绩状态)\nVALUES ('2004310431xxxxx62789150','2004310431 ','10204db    ','10','20','70',NULL,'100',NULL,NULL,'1')"
                              };

            for (int i = 0; i < insert.Length; i+=2)
            {
                arg1 = new StringBuilder(insert[i]);
                Console.WriteLine(insert[i]);
                Console.ReadKey(false);
                parseExe(arg1, ref arg2);
                Console.WriteLine(arg2);
                Console.WriteLine();

                Console.ReadKey(false);
                arg1 = new StringBuilder(insert[i+1]);
                Console.WriteLine(insert[i+1]);
                Console.ReadKey(false);
                parseExe(arg1, ref arg2);
                Console.WriteLine(arg2);
                Console.WriteLine();

                Console.ReadKey(false);
                arg1 = new StringBuilder(insert[i]);
                Console.WriteLine(insert[i]);
                Console.ReadKey(false);
                parseExe(arg1, ref arg2);
                Console.WriteLine(arg2);

                Console.ReadKey(false);
                Console.Clear();
            }

            string[] update = {"SELECT * from t_Department WHERE 院系号 = '07'",
                              "UPDATE t_Department \nSET 院系名 = '现代生物信息学系' \nWHERE 院系号 = '07'",
                              "SELECT * FROM t_Score WHERE 学号 = '2004123061 '",
                              "UPDATE t_Score \nSET 总成绩 = '99' \nWHERE 学号 = '2004123061 '",
                              "SELECT * FROM t_Score WHERE 课程号 = '001006     '",
                              "UPDATE t_Score \nSET 平时成绩 = NULL \nWHERE 课程号 = '001006     '"
                              };
            for (int i = 0; i < update.Length; i += 2)
            {
                arg1 = new StringBuilder(update[i]);
                Console.WriteLine(update[i]);
                Console.ReadKey(false);
                parseExe(arg1, ref arg2);
                Console.WriteLine(arg2);
                Console.WriteLine();

                Console.ReadKey(false);
                arg1 = new StringBuilder(update[i + 1]);
                Console.WriteLine(update[i + 1]);
                Console.ReadKey(false);
                parseExe(arg1, ref arg2);
                Console.WriteLine(arg2);
                Console.WriteLine();

                Console.ReadKey(false);
                arg1 = new StringBuilder(update[i]);
                Console.WriteLine(update[i]);
                Console.ReadKey(false);
                parseExe(arg1, ref arg2);
                Console.WriteLine(arg2);

                Console.ReadKey(false);
                Console.Clear();
            }

            string[] delete = { "SELECT * FROM t_Score WHERE 学号='2004310431 '",
                                  "DELETE FROM t_Score \nWHERE 学号='2004310431 '",
                                  "SELECT * FROM t_StuCourse WHERE 所选课程号='022002     ' OR 选课IP IS NULL",
                                  "DELETE FROM t_StuCourse \nWHERE 所选课程号='022002     ' OR 选课IP IS NULL",
                                  "SELECT * FROM t_Stu WHERE 入学年月='200309' AND 姓名 LIKE '张%'",
                                  "DELETE FROM t_Stu \nWHERE 入学年月='200309' AND 姓名 LIKE '张%'"
                              };

            for (int i = 0; i < delete.Length; i += 2)
            {
                arg1 = new StringBuilder(delete[i]);
                Console.WriteLine(delete[i]);
                Console.ReadKey(false);
                parseExe(arg1, ref arg2);
                Console.WriteLine(arg2);
                Console.WriteLine();

                Console.ReadKey(false);
                arg1 = new StringBuilder(delete[i + 1]);
                Console.WriteLine(delete[i + 1]);
                Console.ReadKey(false);
                parseExe(arg1, ref arg2);
                Console.WriteLine(arg2);
                Console.WriteLine();

                Console.ReadKey(false);
                arg1 = new StringBuilder(delete[i]);
                Console.WriteLine(delete[i]);
                Console.ReadKey(false);
                parseExe(arg1, ref arg2);
                Console.WriteLine(arg2);

                Console.ReadKey(false);
                Console.Clear();
            }



            string[] drop = {"DROP TABLE t_Class",
                               "DROP TABLE t_Course",
                               "DROP TABLE t_Department",
                               "DROP TABLE t_Score",
                               "DROP TABLE t_Specialty",
                               "DROP TABLE t_Stu",
                               "DROP TABLE t_StuCourse"
                           };


            for (int i = 0; i < drop.Length; i++)
            {
                arg1 = new StringBuilder(drop[i]);
                Console.WriteLine(drop[i]);
                Console.ReadKey(false);
                parseExe(arg1, ref arg2);
                Console.WriteLine(arg2);
            }

            string[] dropCheck = {"SELECT * FROM t_Class",
                               "SELECT * FROM t_Course",
                               "SELECT * FROM t_Department",
                               "SELECT * FROM t_Score",
                               "SELECT * FROM t_Specialty",
                               "SELECT * FROM t_Stu",
                               "SELECT * FROM t_StuCourse"
                           };


            for (int i = 0; i < dropCheck.Length; i++)
            {
                arg1 = new StringBuilder(dropCheck[i]);
                Console.WriteLine(dropCheck[i]);
                Console.ReadKey(false);
                parseExe(arg1, ref arg2);
                Console.WriteLine(arg2);
            }

            Console.WriteLine("Thank you for seeing the Demo, bye!");
            Console.ReadKey(false);
            mainCloseDB();
            

        }
    }
}
